# -*- coding: utf-8 -*-
import urllib,urllib2, cookielib,re
from datetime import datetime,timedelta
from win32com.client import Dispatch
import time,sqlite3
import os,sys
# import json

# oConn = Dispatch('ADODB.Connection')
# oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\data\data.mdb;Jet OLEDB:Database Password=tp@chenkai"
# oConn.Open()
def getexistctnno():        
    ctnno=[]
    # search_date=datetime.timedelta(days=-7)
    search_date=datetime.strftime(datetime.now()+timedelta(days=-7),"%Y-%m-%d")
    print search_date
    # oRS.Open("select xh from yb where xh<>'' and xdrq>=#"+search_date+"#")
    oRS = Dispatch('ADODB.RecordSet')
    oRS.ActiveConnection = oConn    # Set the recordset to connect thru oConn
    oRS.Open("select id,xh from yb where xh<>'' and xh_info is null and rq>=#"+search_date+"#")
    while not oRS.EOF:
        # print oRS.Fields("cd_no").Value
        i={}
        i["id"]=oRS.Fields("id").Value
        i["xh"]=oRS.Fields("xh").Value
        ctnno.append(i)
        oRS.MoveNext()
    print u"查找到箱号：",ctnno
    oRS.Close()
    oRS=None
    return ctnno
def code(req):
    while True:
        pic=urllib2.urlopen(req).read()
        # pic_name="old.jpg"
        # f = open(pic_name, 'wb')
        # f.write(pic)
        # f.close
        # time.sleep(5)
        # box=(0,0,60,20)
        im=StringIO.StringIO(pic)
        # im.save(pic_name)
        im=Image.open(im)
        # im=im.crop(box)
        # im=im.filter(ImageFilter.MedianFilter())
        # enhancer = ImageEnhance.Contrast(im)
        # im = enhancer.enhance(1)
        # im = im.convert('L')
        # im.show()
        im.save("code.jpg")

        os.popen('tesseract.exe code.jpg code') 
        t=open('code.txt').read()
        print "txt:",t
        pattern=r'[0-9]'
        t=re.findall(pattern,t)
        if(len(t)==4):
            return "".join(t)
        # print "check:","".join(t)
def login(str):
    pattern=r'<font color="#FF3300" >GUEST</font>'
    # print re.search(pattern,str).group(0)
    return re.search(pattern,str)
def searchctnno(ctnno):
    # print ctnno["xh"]
    url='http://www.npedi.com/edi/scodecoIngateAction.do?ctnno='+ctnno["xh"]
    search_str=urllib2.urlopen(url).read()
    # print search_str
    r = re.compile(r'<font color=#FF3300>.+?</font>')
    not_find=re.search(r,search_str)
    
    if not_find:
        print 'not find'
        return
    else:
        r=re.compile(r'<td height="24" nowrap="nowrap" class="dblue12">.*?</td>',re.DOTALL)
        c=re.findall(r,search_str)
        r=re.compile(r'<.+?>|\n|\r',re.DOTALL)
        print len(c),u"共"+str(len(c)/13)+u"条"
        ll=[]
        for i in range(len(c)/13):
            d=[]
            for x in c[i*13+1:i*13+13]:
                d.append(re.sub(r,'',x).strip())
                # print x
            xh_info="|".join(d)
            print xh_info
            try:
                bjsj=datetime.strptime(d[1],"%Y-%m-%d %H:%M")
            except:
                bjsj=datetime.strptime(d[0],"%Y-%m-%d %H:%M:%S")
            ll.append((bjsj,xh_info,d[4]))
        ll.sort()
        ll.reverse()
        print ll
        oCmd = Dispatch('ADODB.Command')
        oCmd.ActiveConnection = oConn
        oCmd.CommandText = "update yb set xh_info='"+str(ll[0][1])+"',tdh='"+str(ll[0][2])+"' where cdate(rq&' '&sj)<#"+datetime.strftime(ll[0][0],"%Y-%m-%d %H:%M")+"# and id="+str(ctnno["id"])
        print oCmd.CommandText
        try:
            oCmd.Execute()
        except:
            print "error"
        oCmd=None
# ##########################################    
# 主程序开始
# ##########################################
# 定义查询的日期        
# 定义更新的报关单数量
# 使用cookie
cookie_support= urllib2.HTTPCookieProcessor(cookielib.CookieJar())
opener = urllib2.build_opener(cookie_support, urllib2.HTTPHandler)
urllib2.install_opener(opener)
'''
###########################################
# 登录信息
pic_req=urllib2.Request(url ="http://www.nbeport.gov.cn/user/imageCheck")
# print code(pic_req)
login_post_data=urllib.urlencode({
    'loginUserId':'rtw',
    'loginUserPassword':'nbo168',
    'smsCode':'',
    'loginCertificationCode':code(pic_req)
})
# 登录的地址
login_req = urllib2.Request(
    url = 'http://www.nbeport.gov.cn/user/LoginServlet?methodName=doLogin',
    data = login_post_data
)

print urllib2.urlopen(login_req).read()
'''
######################################
login_post_data=urllib.urlencode({
    'login-form-type':'pwd',
    'password':'nbo168',
    'username':'rtw'
})
# 登录的地址
login_req = urllib2.Request(
    url = 'http://www.nbeport.gov.cn/pkmslogin.form',
    data = login_post_data
)

login_res=urllib2.urlopen(login_req).read()
# print login_res
######################################
billNo='CNNBO620368'
billNo='W232104653'

def getBillNo():
    conn=sqlite3.connect(r'g:\web\qp4\qp.db3')
    conn.text_factory = str
    conn.row_factory = sqlite3.Row
    
    billnos=conn.execute('select distinct bill_no from dec_mess_status where bill_no is not null').fetchall()
    conn.close()
    return billnos


def getQuery(billNo):
    result=urllib2.urlopen('http://www.nbeport.gov.cn/apdev/proDes/query/manifestQueryAction.do?ec_s_check_rec_time=desc&ec_crd=1000&billNo=%s&msgType=PREMFT&flag=F&methodName=doResultQuery'%billNo).read()
    # print result
    result=re.sub(r'[\t\r\n]','',result)
    # print result
    # tbody=re.search(r'<tbody class="tableBody" >(.+?)</tbody>',result,re.DOTALL).group()
    # print tbody
    trs=re.findall('<tr class="odd" >(.+?)</tr>',result,re.DOTALL)
    for tr in trs:
        tds=re.findall('<td>(.+?)</td>',tr,re.DOTALL)
        tds=[td.strip().decode('gbk') for td in tds]
        conn.execute('insert into manifest values(?,?,?,?,?,?,?,?,?,?,?,?)',tds)

    # if len(trs)>=10:
        # print len(trs),billNo
        # print trs

conn=sqlite3.connect(r'g:\web\nbeport\nbeport.db3')
conn.text_factory = str
conn.row_factory = sqlite3.Row
for x in getBillNo():
    # print x
    getQuery(x[0].strip())
conn.commit()
conn.close()
'''
trs=re.findall('<tr class="odd" >(.+?)</tr>',result,re.DOTALL)
print trs
for tr in trs:
    tds=re.findall('<td>(.+?)</td>',tr,re.DOTALL)
    print tds
    for td in tds:
        print td.strip()
        
'''      
        
# pass
# bLogin=login(urllib2.urlopen(login_req).read())
    
# if bLogin:
    # print "login"
    # for x in getexistctnno():
        # try:
            # print "process...",x
            # searchctnno(x)
        # except:
            # print "error:",x
            # continue

# oConn.Close()
# oConn=None
